﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Wrappers.WebContext;

namespace Dataglot.CrossDb
{
    /// <summary>
    /// Database creation is rather provider specific. This attempts to create a db using
    /// discovery instead of configuration.
    /// </summary>
    public class ConnectToMaster
    {

        //TODO: This class isn't very generic. It needs to be able to cope with any provider
        //that can create a db and any db name.

        private DbProviderFactory provider = DbProviderFactories.GetFactory("System.Data.SqlClient");
        private DbConnection con;

        public ConnectToMaster()
        {
            con = provider.CreateConnection();
        }

        public string WriteConnectionStringToConfigFile(string connectionString)
        {
            //Because discovery is slow, we now need to write/edit a config file.
            //This will only work in devlepment scenarios with full trust.
            throw new NotImplementedException();
        }

        /// <summary>
        /// Discover a server and connect, for use in one time DB installation.
        /// </summary>
        /// <remarks>
        /// This is for database installation, not for looking up the connection string.
        /// </remarks>
        /// <returns></returns>
        public string GuessConnectionStringAndConnect()
        {
            //provider = ;
            
            if (con == null)
            {
                throw new InvalidOperationException(
                    "This provider.CreateConnection() returned null, maybe this provider doesn't support this.");
            }

            //This can fail to return anything useful.
            DataTable table = SqlDataSourceEnumerator.Instance.GetDataSources();

            string firstGuess="";
            
            var guesses = (from r in table.Rows.Cast<DataRow>()
                                   where
                                       (r["ServerName"].ToString() == Environment.MachineName)
                                       &&
                                       ((r["Version"].ToString() == "" ? 0 : new Version(r["Version"].ToString()).Major) >
                                        8)
                                   select r["InstanceName"]);
            
            if(guesses.Count()>0)
            {
                firstGuess = @".\" + guesses.First();
            }

            //Well, try the local SQLExpress anyhow.
            //It could be the wrong version.
            if (string.IsNullOrEmpty(firstGuess))
            {
                firstGuess = @".\SQLEXPRESS";
            }

            con.ConnectionString = GuessConnectionString(firstGuess);
            
            try
            {
                con.Open();
            }
            catch (SqlException ex)
            {
                if (
                    ex.Message.Contains(
                        "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"))
                {
                    con.ConnectionString = GuessConnectionString(".");
                    con.Open();
                }
            }

            return con.ConnectionString;
        }

        //SQL express and local host are most common.
        public string GuessConnectionString(string server)
        {
            DbConnectionStringBuilder builder = provider.CreateConnectionStringBuilder();
            if (builder == null)
            {
                throw new InvalidOperationException("This provider.CreateConnectionStringBuilder returned null, maybe this provider doesn't support this.");
            }
            builder.Add("Data Source", server);//Expected in developer scenarios
            builder.Add("Initial Catalog", "master"); //always exists, always have access to it.
            builder.Add("Integrated Security", "SSPI"); //Expected in developer scenarios
            builder.Add("Connect Timeout", 5);//We are impatient
            return builder.ConnectionString;
        }

        public List<string> ListDbs(DbConnection predeterminedConnection)
        {
            if (predeterminedConnection.Database != "master")
            {
                predeterminedConnection.ConnectionString =  @"Data Source=.\SQLExpress;Initial Catalog=master;Integrated Security=SSPI;";
            }
            

            if (predeterminedConnection.State == ConnectionState.Closed)
            {
                predeterminedConnection.Open();
            }

            List<string> dbs = new List<string>();
            using (DbCommand command = provider.CreateCommand())
            {
                command.Connection = predeterminedConnection;
                command.CommandText = "SELECT name FROM master..sysdatabases";
                using (DbDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult))
                {
                    //there will always be at least one.
                    while (reader.Read())
                    {
                        dbs.Add(reader.GetString(0));
                    }
                }
            }
            return dbs;
        }

        public List<string> ListDbs()
        {
            if (con == null)
                GuessConnectionStringAndConnect();
            return ListDbs(con);
        }

        public void RemoveDataBase(string name)
        {
            string sql =  string.Format(@"IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'{0}')
    DROP DATABASE [{0}]",name);

            using (DbCommand command = provider.CreateCommand())
            {
                command.Connection = con;
                SqlConnection sqlcon = (SqlConnection)con;
                sqlcon.InfoMessage += new SqlInfoMessageEventHandler(sqlcon_InfoMessage);
                command.CommandText = sql;
                int result= command.ExecuteNonQuery();
                Debug.WriteLine("Db drop command ran with output integer of "+result);
            }
        }

        private void OpenIfClosed(DbConnection connection)
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
        }

        public string AddDataBase(string name, DbConnection predeterminedConnection)
        {
            OpenIfClosed(predeterminedConnection);
            name = name.Replace("-", "_");
            //Already in master db
            //"use master"
            string sql = string.Format(
                @"
DECLARE @dbname NVARCHAR(100)
SET @dbname = '{0}'
DECLARE @DATADIR NVARCHAR(500)
SET @DATADIR = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1)
DECLARE @datafile NVARCHAR(4000)
                  
/*
CREATE DATABASE [some_db] ON  PRIMARY 
( NAME = N'some_db', FILENAME = @File , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'some_db_log', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\some_db_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

-- SIZE = 1024, -- use default, same as model.
-- SIZE = 1024, 
*/

declare @sql nvarchar(4000)
set @sql = 'CREATE DATABASE [' + @dbname + +']  
        ON (NAME = ' + @dbname + ', 
                FILENAME = ''' + + @DATADIR + @dbname + '.mdf'',  FILEGROWTH = 10%) 
        LOG ON (NAME = ''' + @dbname + '_log'', 
                        FILENAME = ''' + @DATADIR + @dbname  + '_log.ldf'', 
                        
                        FILEGROWTH = 10%)'
PRINT @SQL
EXEC(@sql)", name);

            using (DbCommand command = provider.CreateCommand())
            {
                command.Connection = predeterminedConnection;
                SqlConnection sqlcon = (SqlConnection)predeterminedConnection;
                sqlcon.InfoMessage += new SqlInfoMessageEventHandler(sqlcon_InfoMessage);
                command.CommandText = sql;
                int result = command.ExecuteNonQuery();
                Debug.WriteLine("Db create command ran with output integer of " + result);
            }
            return name;
        }

        public string AddDataBase(string name)
        {
            return AddDataBase(name,con);
        }

        void sqlcon_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            Debug.WriteLine(e.Message);
        }

        public string CreateDatabaseIfNeeded(SqlConnection predeterminedConnection, string databaseName)
        {
            //Server has been identified
            //Connection string needs to be cached, since it is expensive to keep detecting a db

            string connectionString = predeterminedConnection.ConnectionString;
            if (ListDbs(predeterminedConnection).Contains("hicmah"))
            {
                connectionString = connectionString.Replace("Initial Catalog=master", "Initial Catalog=" + databaseName);
            }
            else
            {
                //create default db!
                string name = AddDataBase(databaseName, predeterminedConnection);
                if (name != databaseName)
                {
                    throw new InvalidOperationException("Expected name of " + databaseName);
                }
                connectionString = connectionString.Replace("Initial Catalog=master", "Initial Catalog=hicmah");
            }

            return connectionString;
        }
    }
}
